This article has moved to this location.
In this article, i would like to show you how to create a simple excel explorer. This explorer is very simple, because an user application only able to browse or download documents (in the PDF format) that has been uploaded previously.
Below are list of libraries used in this article:
The data for each uploaded files are stored in the database (filename, worksheet(s) name, and path to the stored worksheet data) . Below is the schema used in the application:
<database name="propel" defaultIdMethod="native" noxsd="true" package="lib.model.ExcelExplorer">
<table name="excel">
<column name="id" type="INTEGER" required="true" primaryKey="true" autoincrement="true" />
<column name="name" type="VARCHAR" size="255" required="true" />
<column name="created_at" type="TIMESTAMP" />
</table>
<table name="excel_sheet">
<column name="id" type="INTEGER" required="true" primaryKey="true" autoincrement="true" />
<column name="excel_id" type="INTEGER" required="true" />
<foreign-key foreignTable="excel" onDelete="cascade">
<reference local="excel_id" foreign="id" />
</foreign-key>
<column name="name" type="VARCHAR" size="255" required="true" />
<column name="data" type="VARCHAR" size="255" required="true" />
<column name="created_at" type="TIMESTAMP" />
</table>
</database>
<!--layout_excel.php-->
<?php use_helper('blueprint') ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<?php include_javascripts() ?>
<?php include_stylesheets() ?>
</head>
<body>
<div class="container">
<hr>
<h2 class="alt">EXCEL EXPLORER</h2>
<hr>
<div id="content">
<?php echo $sf_content ?>
</div>
<hr>
</div>
</body>
</html>
.sheet {height: 600px; overflow: auto; position: relative; margin: 0;padding: 0;}
#loading, .togglebox {display: none;}
h2.trigger {
cursor: pointer;
padding:5px;
color:#243953;
background-color:#fafafa;
border: 1px solid #a9a9a9;
/* Rounder Corner */
-moz-border-radius: 7px;
-webkit-border-radius: 7px;
-khtml-border-radius: 7px;
text-align:center;
font-size:12px;
font-family:Georgia, "Times New Roman", Times, serif;
margin-bottom:15px;
width: 120px;
}
.togglebox {
border: 1px solid #a9a9a9;
/* Rounder Corner */
-moz-border-radius: 7px;
-webkit-border-radius: 7px;
-khtml-border-radius: 7px;
overflow: hidden;
clear: both;
margin-bottom:10px;
width: 295px;
}
.togglebox .content { padding: 10px 10px 0 15px; }
.push-0 {margin:0 0 1.5em 30px;float:right;position:relative;}
Before we going to further, let us create the main module. In this article is excelExplorer (./symfony generate:module backend excelExplorer).
Create config folder inside excelExplorer module and create a view.yml inside the config folder you’ve just created
all: stylesheets: [-*, excel_explorer.css, /jquery-treeview/jquery.treeview.css] javascripts: [/jquery-treeview/jquery.treeview.js] layout: layout_excel
Modify it’s executeIndex() function:
public function executeIndex(sfWebRequest $request)
{
$this->form = new ExcelForm();
$this->excels = ExcelPeer::doSelect(new Criteria());
}
In the executeIndex() method, first it’s create the upload form, then calls the ExcelPeer Model to retrieve all the available excel file names, and then displaying them in the tree form.
Let us open the indexSuccess.php file and modify it’s content.
<!--indexSuccess.php-->
<?php use_helper('jQuery') ?>
<!--START: upload form-->
<h2 class="trigger">Upload Your File Here</h2>
<div class="togglebox">
<div class="content">
<div id="uploader">
<?php include_partial('excelExplorer/upload_form', array('form'=>$form)) ?>
</div>
</div>
</div>
<!--END: upload form-->
<hr/>
<!--START: excels tree-->
<div class="span-5 colborder">
<ul id="browser" class="filetree">
<?php foreach ($excels as $x): ?>
<li>
<?php echo image_tag('/jquery-treeview/images/folder.gif') ?><?php echo $x->getName() ?>
<ul>
<?php foreach ($x->getExcelSheets() as $s): ?>
<li><?php echo link_to(image_tag('/jquery-treeview/images/file.gif').$s->getName(), 'excelExplorer/getData?excel_sheet_id='.$s->getId(), 'class=ajax-links') ?></li>
<?php endforeach; ?>
</ul>
</li>
<?php endforeach; ?>
</ul>
</div>
<!--END: excels tree-->
<div id="loading">
<img src="/images/loading.gif" alt="Loading..." />
</div>
<div class="span-17 push-0" id="sheet">
<!-- data will be displayed here -->
</div>
<script type="text/javascript">
jQuery(function() {
jQuery("#browser").treeview();
});
</script>
and below is the partial for the upload form.
<!-- _upload_form.php -->
<form id="upload_form" action="<?php echo url_for('excelExplorer/upload') ?>" method="post" enctype="multipart/form-data">
<?php echo $form ?>
<p><input type="submit" value="Submit"></p>
</form>
First, open your application’s app.yml file, and add the settings below:
all:
excel_explorer:
upload_dir: %SF_UPLOAD_DIR%/excel
images_root: uploads/excel
When an user click the Upload Your File Here button, the upload form will be displayed as shown in the picture above.
Alright.. let us add jquery code after jQuery(“#browser”).treeview();
<script type="text/javascript">
jQuery("#browser").treeview();
jQuery("h2.trigger").click(function(){
jQuery(this).next(".togglebox").slideToggle("slow");
return true;
});
</script>
Now, let’s have a closer look at the _partial_form.php file above. When the user press the submit button, the upload action will be executed.
Open the actions.class.php and add the function below
public function executeUpload(sfWebRequest $request)
{
$form = new ExcelForm();
$form->bind($request->getParameter($form->getName()), $request->getFiles($form->getName()));
if ($form->isValid())
{
$excel = $form->save();
$this->getUser()->setFlash('notice', 'The file was successfully uploaded.');
return $this->redirect('excelExplorer/index');
}
$this->form = $form;
$this->setTemplate('uploadForm');
}
and create uploadFormSuccess.php (in the case user failed to upload the file)
<!-- uploadFormSuccess.php -->
<?php include_partial('excelExplorer/upload_form', array('form'=>$form)) ?>
Alright, enough for controller and views.. let’s go to the next steps now.
First, let us create a new class that inherited from PHPExcel_Writer_HTML.
The saveDataOnly function has two parameters. The first parameter is the file name and the second parameter is the flag to indicate whether to store the file into the disk or not. If you want to store the result into the disk, you have to provide the desired filename and set the second parameter to true, but if you want to get the generated html only, leave the parameters as default.
If you have several images in your excel’s file, by default PHPExcel_Writer_HTML won’t save the images into the disk. In order to achieve those feature we have to override the _writeImageTagInCell function, but unfortunately.. the _writeImageTagInCell function is a private function, so we have to change it first to protected (this is bad solution, if you have another solution, please let me know) .
Alright.. open the HTML.php in the plugins/sfPhpExcelPlugin/lib/PHPExcel/PHPExcel/Writer directory, search for the _writeImageTagInCell function and change the private keyword with protected.
<?php
class myPHPExcel_Writer_HTML extends PHPExcel_Writer_HTML
{
protected $imageDir = null;
protected $imagePath = null;
public function saveDataOnly($pFilename = null, $bSaveToDisk = false) {
$retval = '';
PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized;
$this->_phpExcel->garbageCollect();
$saveArrayReturnType = PHPExcel_Calculation::getArrayReturnType();
PHPExcel_Calculation::setArrayReturnType( PHPExcel_Calculation::RETURN_ARRAY_AS_VALUE);
$this->setUseInlineCss(true);
$this->buildCSS(!$this->getUseInlineCss());
$retval = $this->generateSheetData();
if ($bSaveToDisk) {
$directory = dirname($pFilename);
if (!is_readable($directory)) {
if (!@mkdir($directory, 0777, true)) {
throw new Exception(sprintf('Failed to create file upload directory "%s".', $directory));
}
chmod($directory, 0777);
}
$fileHandle = fopen($pFilename, 'w+');
if ($fileHandle === false) {
throw new Exception("Could not open file $pFilename for writing.");
}
fwrite($fileHandle, $retval);
fclose($fileHandle);
$retval = $pFilename;
}
PHPExcel_Calculation::setArrayReturnType($saveArrayReturnType);
$saveArrayReturnType = PHPExcel_Calculation::getArrayReturnType();
PHPExcel_Calculation::setArrayReturnType( PHPExcel_Calculation::RETURN_ARRAY_AS_VALUE);
return $retval;
}
protected function getImageDir()
{
if (null === $this->imageDir)
{
$imageDir = DIRECTORY_SEPARATOR.'images'.DIRECTORY_SEPARATOR;
if (substr($this->getImagesRoot(), 0, 1) != '.') {
if (substr($this->getImagesRoot(), 0, 1) == '/') {
$imageDir = $this->getImagesRoot().DIRECTORY_SEPARATOR;
}
else {
$imageDir = DIRECTORY_SEPARATOR.$this->getImagesRoot().DIRECTORY_SEPARATOR;
}
}
if (substr($this->getImagesRoot(), 0, 2) == './') {
$imageDir = substr($this->getImagesRoot(), 1).DIRECTORY_SEPARATOR;
}
$imagePath = sfConfig::get('sf_web_dir').$imageDir;
if (!is_readable($imagePath)) {
if (!@mkdir($imagePath, 0777, true)) {
throw new Exception(sprintf('Failed to create file upload directory "%s".', $imagePath));
}
chmod($imagePath, 0777);
}
$this->imageDir = $imageDir;
$this->imagePath = $imagePath;
}
return $this->imageDir;
}
protected function _writeImageTagInCell(PHPExcel_Worksheet $pSheet, $coordinates)
{
$html = '';
$imageDir = $this->getImageDir();
foreach ($pSheet->getDrawingCollection() as $drawing) {
$imageSrc = null;
if ($drawing instanceof PHPExcel_Worksheet_Drawing) {
if ($drawing->getCoordinates() == $coordinates) {
$filename = $drawing->getPath();
if (substr($filename, 0, 1) == '.') {
$filename = substr($filename, 1);
}
$imageSrc = $imageDir.$drawing->getIndexedFilename();
$filePath = $this->imagePath.DIRECTORY_SEPARATOR.$drawing->getIndexedFilename();
copy($filename, $filePath);
}
}
else if ($drawing instanceof PHPExcel_Worksheet_MemoryDrawing) {
if ($drawing->getCoordinates() == $coordinates) {
$filename = $drawing->getIndexedFilename();
$imageSrc = $imageDir.$filename;
$filePath = $this->imagePath.DIRECTORY_SEPARATOR.$drawing->getIndexedFilename();
$image = $drawing->getImageResource();
switch ($drawing->getRenderingFunction()) {
case PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG:
imagejpeg($image, $filePath);
break;
case PHPExcel_Worksheet_MemoryDrawing::RENDERING_GIF:
imagegif($image, $filePath);
break;
case PHPExcel_Worksheet_MemoryDrawing::RENDERING_PNG:
case PHPExcel_Worksheet_MemoryDrawing::RENDERING_DEFAULT:
imagepng($image, $filePath);
break;
}
}
}
if ($imageSrc !== null)
{
$html .= "\r\n";
$html .= ' <img style="position: relative; left: ' . $drawing->getOffsetX() . 'px; top: ' . $drawing->getOffsetY() . 'px; width: ' . $drawing->getWidth() . 'px; height: ' . $drawing->getHeight() . 'px;" src="' . $imageSrc . '" border="0" width="' . $drawing->getWidth() . '" height="' . $drawing->getHeight() . '" />' . "\r\n";
}
}
return $html;
}
}
Now, open ExcelForm.class.php file, and modify it’s configure function. Remove unused fields except field name and change the widget to the sfWidgetFormInputFile
public function configure()
{
$this->useFields(array('name'));
$this->widgetSchema['name'] = new sfWidgetFormInputFile();
$this->widgetSchema['name']->setLabel('Excel File');
$this->validatorSchema['name'] = new sfValidatorFile(
array('required'=>true,
'mime_type_guessers' => array(),
'mime_types'=>array('application/excel', 'application/vnd.ms-excel', 'application/x-msexcel', 'application/vnd.oasis.opendocument.spreadsheet')), array('required'=>'Please fill with the file you want to upload', 'mime_types'=>'The uploaded file should be an excel file'));
}
When symfony updating an object, symfony will pre-process the value(s) first and search for updateXXXColumn(). If it’s found, symfony will process this function to pre-process the value(s).
Alright… let us add an updateNameColumn function to our ExcelForm
public function updateNameColumn($value)
{
if (!$value)
{
return false;
}
$this->getObject()->extractExcel($value->getTempName(), $value->getOriginalExtension(), $value->getOriginalName());
return $value->getOriginalName();
}
The function above simply return the original filename as the result and delegate the reading process (sheet by sheet) to the housed object (Excel class).
Open the Excel.class.php, and add the function below
public function extractExcel($path, $ext, $folder = null)
{
$uploadDir = sfConfig::get('app_excel_explorer_upload_dir', sfConfig::get('sf_upload_dir'));
switch ($ext)
{
case '.xlsx':
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
break;
default:
$objReader = PHPExcel_IOFactory::createReader('Excel5');
}
$objPHPExcel = $objReader->load($path);
$htmlWriter = new myPHPExcel_Writer_HTML($objPHPExcel);
$imagesRoot = sfConfig::get('app_excel_explorer_images_root', 'images');
if (null !== $folder) {
$folder = myUtils::slugify($folder);
$imagesRoot .= DIRECTORY_SEPARATOR.$folder;
$uploadDir .= DIRECTORY_SEPARATOR.$folder;
}
//image root relative to the document root
$htmlWriter->setImagesRoot($imagesRoot);
foreach ($objPHPExcel->getAllSheets() as $key => $worksheet)
{
$file = $uploadDir.DIRECTORY_SEPARATOR.md5($worksheet->getTitle().time()).'.html';
$htmlWriter->setSheetIndex($key);
$htmlWriter->saveDataOnly($file, true);
$excelSheet = new ExcelSheet();
$excelSheet->setName($worksheet->getTitle());
$excelSheet->setData($file);
$this->addExcelSheet($excelSheet);
}
$objPHPExcel->disconnectWorksheets();
unset($objPHPExcel);
return $this;
}
The function above has 3 parameters:
slugify method is taken from jobeet tutorial
Let’s have a closer look at the indexSuccess.php file line 23
<?php echo link_to(image_tag('/jquery-treeview/images/file.gif').$s->getName(), 'excelExplorer/getData?excel_sheet_id='.$s->getId(), 'class=ajax-links') ?>
when a user click a link above, the data will be loaded using ajax and displayed in the sheet tag. Open indexSuccess.php file, and append the code below:
<script type="text/javascript">
//....
var loading = jQuery("#loading");
jQuery("a.ajax-links").bind('click',function(event){
showLoading();
jQuery('#sheet').hide();
event.preventDefault();
jQuery.get(this.href,{},function(response){
jQuery('#sheet').html(response);
jQuery('#sheet').show();
hideLoading();
});
});
function showLoading(){
loading.css({visibility:"visible"}).css({opacity:"1"}).css({display:"block"});
};
function hideLoading(){
loading.fadeTo(1000, 0);
loading.hide();
};
</script>
Open actions.class.php and add function below
public function executeGetData(sfWebRequest $request)
{
sfConfig::set('sf_web_debug', false);
$this->setLayout(false);
$this->excelSheet = ExcelSheetPeer::retrieveByPk($request->getParameter('excel_sheet_id'));
}
Create getDataSuccess.php add the code below
<div class="sheet"> <?php $content = $excelSheet ? file_get_contents($excelSheet->getData()) : false ?> <?php echo $content === false ? 'oopps.. file not found' : $content ?> </div>
Library used to generating pdf file is TCPDF, that already packaged with PHPExcel library (but you can use your favorite library).
Open the getDataSuccess.php modify it’s content and add the code below. So it’ll look like the following:
<p class="push-0">
<?php echo link_to(image_tag('pdficon.png'), 'excelExplorer/pdf?excel_sheet_id='.$excelSheet->getId(), 'title=Export to PDF') ?>
<hr/>
<div class="sheet">
<?php $content = $excelSheet ? file_get_contents($excelSheet->getData()) : false ?>
<?php echo $content === false ? 'oopps.. file not found' : $content ?>
</div>
</p>
Open the actions.class.php, and add the functions below:
public function executePdf(sfWebRequest $request)
{
$this->forward404Unless($excelSheet = ExcelSheetPeer::retrieveByPk($request->getParameter('excel_sheet_id')));
$this->forward404Unless($content = file_get_contents($excelSheet->getData()));
$pdf = new TCPDF('P', 'pt');
$pdf->setPrintHeader(false);
$pdf->setPrintFooter(false);
$pdf->AddPage();
$pdf->writeHTML($content);
$pdf->Output($excelSheet->getName().'.pdf', 'D');
}
Hope it’s useful.. thanks you.. and sorry for my bad english 😉
 | parisss on how to change select options b… |
 | Rodrigo Traleski on Symfony and Ioncube |
| Rodrigo Traleski on Symfony and Ioncube |
 | article directory li… on Ajaxify Symfony Admin Gen… |
 | Prasad on Combining input and choice wid… |
 | Charge on ‘Pick or create’ e… |
 | DrAkE on Generating pdf using mPDF libr… |
Just Blog, No More... 
Excelente aporte. Muy bueno!!
gracias 😉
Hi, I love your tutorials (clever, interesting mixes).
As a symfony fan, I tried this one.
But there is a trouble with the sfJqueryReloadedPlugin which is not much compatible with symfony 1.4.6.
sfJqueryReloadedPlugin repositories are just for sf1.2 and below. When I tried : symfony install:plugin sfJqueryReloadedPlugin, this works but after that it is out of order when needed.
How do you install sfJqueryReloadedPlugin with sf1.4.7Dev ?
Thanks in advance.
download sfJqueryReloadedPlugin, unarchive it to the plugins directory, then create sfJqueryReloadedPlugin folder in your web directory, copy css and js folders into it (or you can create symlink if you are using linux distro).
good luck
Indeed, it works as you said (after some tips and tricks…). Thanks.
For your prerequisite : “sfPhpExcelPlugin with PHPExcel version 1.7.3c”, if you install plugin via Pear sf site, you have to replace PHPExcel folder with the 1.7.3c one. Right ?
I still got this error :
Fatal error: Call to undefined method PHPExcel::removeCellStyleXfByIndex() in myapp\plugins\sfPhpExcelPlugin\lib\PHPExcel\PHPExcel\Reader\Excel5.php on line 504
What is your PHPExcel/sfPhpExcelPlugin installation procedure ?
Have you tried with Excel 2003 spreadsheet; or only with 2007 xls ?
Not just the folder (sfPhpExcelPlugin/lib/PHPExcel).. but PHPExcel.php file too..
I’ve tried with both Excel 2003 and 2007. The sample in the figure above is taken from Excel 2003 spreadsheet.
btw.. i never install plugins via pear sf site, i always use the ‘old’ procedure 😉
_ For information, I used : plugin:install (with pear too) and plugin:publish-assets commands, it is equivalent and works fine.
_ You are right. I tried many times and lost this PHPExcel.php on the road last times… (;-)
_ A last little salt : since the beginning, I use Doctrine instead of Propel. And with Doctrine, you can’t use the word ‘data’ for a field since it is a reserved word…
You see the trouble (;-))
It is a tutorial for HSS(High-Sensation Seeker) ? No ?
I changed it to dataexcelsheet and getters/setters names but in Excel.class.php
I imagine PHPExcel need this ‘data’ field, no ?
_ I needed to swap type-mime checker. Otherwise, it tells me my excel file is not excel file …
_ Why are you still using Propel ?
By nostalgy for old times ? (;-)) (I used and still use it with sf1.0 but it seems it would not or less be active in the future than doctrine)
i don’t know if the word data is one of the reserved words in doctrine. I always use propel in my symfony applications.
Btw.. Propel still active .. you can check this link http://www.propelorm.org/timeline
and this is blog about propel http://propel.posterous.com/
Which ORM we have to pick for our projects? i think it’s just a matter of taste
_I am very new to doctrine (just testing) and I saw this message about ‘data’ word which surprised me…and it isn’t new :
http://groups.google.com/group/symfony-devs/browse_thread/thread/37ead55f00320620
http://trac.doctrine-project.org/ticket/1251
>>the solution seems to be in adding quoting marks “data” or using alias…
_I know these 2 links about propel, thanks.
And I know the story of the french guy (Francois Z) who is now behind propel.
He was in conflict with Sf creator (Fabien P) 2 years ago.
Doctrine (Jonathan W) has been chosen as the core orm for further Sf versions (sf1.2+, Sf2.0 coming).
Jonathan W is core member of Symfony team whereas Francois Z is not member anymore, has another outside full-time job and a family and children…
You see the difference ?
It is a strategic choice for future : migrations features, mongodb/doctrine possibilities, more powerfull criteria system, performances… no ?
So I don’t think it is just a matter of taste;
also a question of features for future.
I can bet there will be more and more doctrine plugins compared to propel plugins with time.
So matter of taste, matter of belief and betting…
But perhaps you are right to stay on propel for short/mid term.
Propel 1.5 has powerfull criteria system too.. performances? i think the performance of propel 1.4.x > isn’t much different with doctrine (although i haven’t doing a benchmark). You are right.. propel doesn’t support NoSQL databases for now.. Let hope francois and co will add these feature to the next version of propel
Thanks you
All right.
Good morning Indonesia from France.
Congratulations for your articles.
You are a really clever guy !
Good evening france from Indonesia..
Thanks you for your appreciation..
Btw.. i’m not as clever as you think.. 😉
You know, it is particular to very clever guys not thinking they are clever… (;-)
Are you living as an independant developer in Indonesia with Sf technologies ?
You should try other frameworks like Django or Grails (which are exact equivalent of Sf for Python and Groovy/Java languages).
And what do you think about other Drupal, Joomla, WordPress, Magento, Prestashop ?
By day i work as web developer at Government City of Surabaya (One of Indonesia provinces) and by night i work as web freelancer ;). Yup.. i always use symfony in my projects.
I havent try all of them, except joomla (i’ve used it twice)